package dblayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import modellayer.Customer;

/**
 * The customerDB class to store customer information
 * @author Allan Eriksen, Andreas Rye, Casper Madsen, Christian Stidsborg, Stefan Bols
 */

public class CustomerDB implements IFCustomer 
{
	private Connection con;
	private boolean succes;
	
	/**
	 * CustomerDB Fields
	 * @param con
	 * @param succes
	 */
	
	/**
	 * Constructor for the CustomerDB class
	 */
	public CustomerDB()
	{
		con = DBConnection.getInstance().getDBcon();
	}
	
	/**
	 * Insert method to get customer information in to the database
	 * @param customerObj
	 */
	@Override
	public int insertCustomer(Customer customerObj) throws Exception 
	{
		int rc = -1;
		
		String query = "INSERT INTO Customer (firstName, lastName, address, zipcode, city, phonenumber, club) VALUES ('"+customerObj.getFirstName()+"', '"+customerObj.getLastName()+"', '"+customerObj.getAddress()+"', '"+customerObj.getZipcode()+"', '"+customerObj.getCity()+"', '"+customerObj.getPhonenumber()+"', '"+customerObj.isClub()+"');";
				
		try
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}
		catch(SQLException ex)
		{
			System.out.println("SQL query: "+query);
			System.out.println("	error: "+ex);
			
			throw new Exception("Failed to insert Customer to Database");
		}
		
		return rc;	
	}
	
	
	/**
	 * search method to find customers in the databse
	 * @param customerNo
	 */
	@Override
	public Customer findCustomerByCustomerNo(int customerNo) 
	{
		String wClause = " customerNo='"+customerNo+"'";
		return singleWhere(wClause);
	}

	/**
	 * search method to find customers in the databse
	 * @param phonenumber
	 */
	@Override
	public Customer findCustomerByPhonenumber(String phonenumber) 
	{
		String wClause = " phonenumber='"+phonenumber+"'";
		return singleWhere(wClause);
	}
	
	/**
	 * delete method to remove customers from the databse
	 * @param customerNo
	 */
	@Override
	public boolean deleteCustomer(int customerNo)
	{
		succes = false;
		
		String query = "DELETE FROM Customer WHERE customerNo='"+customerNo+"'";
		
		try 
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			stmt.executeQuery(query);			
			stmt.close();
			succes = true;
		} 
		catch (Exception ex) 
		{
			System.out.println("SQL query: "+query);
			System.out.println("	error: "+ex);
		}
		
		return succes;
	}
	
	/**
	 * update method to update customer information in the databse
	 * @param customerNo
	 * @param firstName
	 * @param lastName
	 * @param address
	 * @param zipcode
	 * @param city
	 * @param phonenumber
	 * @param club
	 */
	@Override
	public boolean updateCustomer(int customerNo, String firstName, String lastName, String address, int zipcode, String city, String phonenumber, boolean club)
	{
		succes = false;
		
		String query = "UPDATE Customer " + "SET firstName='"+firstName+"', lastName='"+lastName+"', address='"+address+"', zipcode='"+zipcode+"', city='"+city+"', phonenumber='"+phonenumber+"', club='"+club+"' " + "WHERE customerNo='"+customerNo+"'";
		
		try
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			stmt.executeUpdate(query);
			stmt.close();
			succes = true;
		}
		catch (SQLException ex)
		{
			System.out.println("SQL query" +query);
			System.out.println("Error: " +ex);
		}
		
		return succes;
	}
	
	/**
	 * singleWhere method to execute a method in the databse
	 * @param wClause
	 * @return customerObj
	 */
	private Customer singleWhere(String wClause) 
	{
		ResultSet results;
		Customer customerObj = null;
		
		String query = buildQuery(wClause);
		
		try 
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			
			if (results.next()) 
			{
				customerObj = buildCustomer(results);
			} 
			else 
			{
				customerObj = null;
			}
			
			stmt.close();
		} 
		catch (Exception ex) 
		{
			System.out.println("SQL query: "+query);
			System.out.println("	error: "+ex);
		}
		
		return customerObj;
	}
	
	/**
	 * build query method to create the query
	 * @param wClause
	 * @return query
	 */
	private String buildQuery(String wClause) 
	{
		String query = "SELECT customerNo, firstName, lastName, address, zipcode, city, phonenumber, club FROM Customer";
		
		if (!wClause.isEmpty()) 
		{
			query += " WHERE " + wClause;
		}
		
		return query;
	}
	
	/**
	 * create method to create a customer from the databse
	 * @param result
	 * @return customerObj
	 */
	private Customer buildCustomer(ResultSet result) 
	{
		Customer customerObj = null;
		try 
		{
			customerObj = new Customer(result.getInt("customerNo"), result.getString("firstName"), result.getString("lastName"), result.getString("address"), result.getInt("zipcode"), result.getString("city"), result.getString("phonenumber"), result.getBoolean("club"));
		} 
		catch (SQLException e) 
		{
			System.out.println("Error building Customer object!\nError: " + e);
		}
		
		return customerObj;
	}

	
}
